package net.atomarrow.domains;

import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.Type;
import java.sql.Time;
import java.util.*;
import java.util.logging.Logger;

import net.atomarrow.db.annotation.Comment;
import net.atomarrow.db.annotation.Default;
import net.atomarrow.db.annotation.FieldType;
import net.atomarrow.db.annotation.Index;
import net.atomarrow.db.annotation.NotCreate;
import net.atomarrow.db.annotation.NotNull;
import net.atomarrow.db.annotation.PrimaryKey;
import net.atomarrow.services.IService;
import net.atomarrow.util.ArrayUtil;
import net.atomarrow.util.map.ListMap;

public class DbMaker {
    /**
     * 只创建，已存在表不处理
     */
    public static final int MODE_CREATE=1;
    /**
     * 不存的创建，存在的表进行更新
     */
    public static final int MODE_UPDATE=2;
    /**
     * 删除已存在的表重新建立
     */
    public static final int MODE_RECREATE=3;

    /**
     * 需要创建
     */
    private  final int DO_CREATE=1;
    /**
     * 需要更新
     */
    private  final int DO_UPDATE=2;
    /**
     * 不需要处理
     */
    private  final int DO_NOTHING=3;

    private  Logger log = null;

    private  Serializable tableSchema;


    public DbMaker(String databaseName){
        this.tableSchema=databaseName;
        log = Logger.getLogger("数据库表管理:");
    }

    public  void makeTable(String tableName,Class domain,IService baseService){
    	if(Domain.class.isAssignableFrom(domain)){
    		makeTable(tableName, domain, baseService, MODE_UPDATE);
    	}
    }


    public  void makeTable(String tableName,Class domain,IService baseService,int Mode){

        List<Column>columns = getNeedCrateColumn(domain);

        int doWhat=dropTableIfNeed(tableName,columns,baseService,Mode);

        switch(doWhat){
        case DO_CREATE:
            String sql =generateCreateSql(tableName,columns);
            baseService.execute(sql);
            processIndex(tableName,columns,baseService);
            log.info(sql);
            log.info(tableName+" created");
            break;

        case DO_UPDATE:
            sql =generateUpdateSql(tableName,columns,baseService);
            processIndex(tableName,columns,baseService);
            if(sql==null){
                log.info(tableName+" not need updated");
                break;
            }
            log.info(sql);
            log.info(tableName+" updated");
            baseService.execute(sql);
            break;

        case DO_NOTHING:
            log.info(tableName+" not need create");
            break;

        }

    }

    /**
     * update index
     * @param columns
     */
    private boolean processIndex(String tableName,List<Column> columns,IService baseService) {
    	boolean flag=false;
        Map<String, String> indexMap = new HashMap<>();
    	for(Column column:columns){
    		if(column.index!=null){
    			flag=true;
    			String columnnNames = indexMap.get(column.index);
    			indexMap.put(column.index, columnnNames == null ? column.columnName : (columnnNames + "," + column.columnName));
    		}
    	}
        for (String index : indexMap.keySet()) {
            boolean execute=updateIndex(tableName,index, indexMap.get(index), baseService);
            if(execute){
                log.info(tableName+" column "+indexMap.get(index)+" add  index "+ index);
            }
        }


    	return flag;
	}

	private boolean updateIndex(String tableName,String indexName, String columns,IService baseService) {
		// check exist
		String sql ="show index from  "+tableSchema+"."+tableName+" where key_name like '"+indexName+"'";
		boolean exist = (baseService.executeUpdate(sql,null)!=0);
    	if(exist){
    		return false;
    	}
    	//update
    	sql="ALTER TABLE "+tableSchema+"."+tableName+" ADD index "+indexName+"  ( "+columns+" ) ";
    	baseService.executeUpdate(sql,null);
    	return true;
	}

	private  String generateUpdateSql(String tableName,
            List<Column> columns, IService baseService) {
        columns=getNeedAddColumns(tableName,columns,baseService);
        for(Column c:columns){
            log.info(tableName+" add new column "+c.columnName);
        }
        /**
         *
        //增加一个新列
         *  alter table tbtest1 add (i int(10) , j int(2));
         */

        StringBuilder sql=new StringBuilder();
        sql.append("ALTER TABLE `");
        sql.append(tableName);
        sql.append("` Add ");
        if(columns.size()==0){
            return null;
        }
        sql.append("(");
        for(Column column:columns){
            processName(sql,column);
            processType(sql,column);
            processUtf8mb4(sql, column);
            processNotNull(sql,column);
            processDefault(sql,column);
            processComment(sql,column);
            sql.append(",");
        }
        sql.deleteCharAt(sql.length()-1);
        sql.append(")");
        return sql.toString();
    }


    private List<Column> getNeedAddColumns(String tableName,
            List<Column> columns, IService baseService) {
    	List<Column> needAddcolumns=new ArrayList<Column>();
        Column column = null;
        for(int i=0;i<columns.size();i++){
            column=columns.get(i);
            if(!checkColumnExist(tableName,column.columnName,baseService)){
                needAddcolumns.add(column);
            }
        }
        return needAddcolumns;
    }


    /**
     * 如果存在返回true
     * @param tableName
     * @param columnName
     * @param baseService
     * @return
     */
    private boolean checkColumnExist(String tableName, String columnName,
            IService baseService) {
        //方案一：
//      Conditions conditions=new Conditions("information_schema.COLUMNS");
//      conditions.putEW("TABLE_SCHEMA", tableSchema);
//      conditions.putEW("TABLE_NAME", tableName);
//      conditions.putEW("COLUMN_NAME", columnName);
//      return baseService.getCount(conditions)>0;
        return baseService.executeUpdate("SHOW COLUMNS FROM "+tableSchema+"."+tableName+" where Field='"+columnName+"'", null)!=0;
    }

    private  int dropTableIfNeed(String tableName,
            List<Column> columns, IService baseService, int mode) {
        boolean tableExist=checkTableExist(tableName,baseService);
        if(!tableExist){
            //表不存在，直接返回需要创建
            return DO_CREATE;
        }
        if(tableExist&&mode==MODE_CREATE){
            //create模式时，表存在不需要创建，也不删除表
            return DO_NOTHING;
        }
        if(mode==MODE_RECREATE){
            dropTable(tableName,baseService);
            return DO_CREATE;
        }
        if(mode==MODE_UPDATE){
            return DO_UPDATE;
        }
        return 0;
    }


    private  void dropTable(String tableName, IService baseService) {
        String dropSql ="drop table "+tableName;
        baseService.execute(dropSql);
        log.info(dropSql);
    }


    /**
     * 如果存在返回true
     * @param tableName
     * @param baseService
     * @return
     */
    private  boolean checkTableExist(String tableName, IService baseService) {
//方案一：
//      Conditions conditions=new Conditions("information_schema.TABLES");
//      conditions.putEW("TABLE_SCHEMA", tableSchema);
//      conditions.putEW("TABLE_NAME", tableName);
//      return baseService.getCount(conditions)>0;
        //方案二：
        return baseService.executeUpdate("SHOW TABLE STATUS LIKE '"+tableName+"'", null)!=0;
    }


    private  List<Column> getNeedCrateColumn(Class domain) {
        Field[] fields = domain.getDeclaredFields();
        List<Column> columns = new ArrayList<Column>(fields.length);
        for(Field f:fields){
            if(needCreate(f)){
                boolean isPrimaryKey=f.getName().equals("id");
                String type=getSqlType(f,isPrimaryKey);
                boolean notNull=hasNotNull(f);
                String defaultValue=getDefaultValue(f);
                String comment=getComment(f);
                String index = getIndex(f);
                boolean utf8mb4=getUtf8mb4(f);
                columns.add(new Column(f.getName(),type,notNull,defaultValue,comment,isPrimaryKey,f.getGenericType(),index,utf8mb4));
            }
        }
        return columns;
    }

    private boolean getUtf8mb4(Field f) {
        if(f.isAnnotationPresent(FieldType.class)){
            FieldType type=f.getAnnotation(FieldType.class);
            switch(type.type()){
                case VARCHAR:
                    return type.utf8mb4();
                case TEXT:
                	return type.utf8mb4();

            }
            return false;
        }
		return false;
	}

	private String getIndex(Field f) {
        if(f.isAnnotationPresent(Index.class)){
        	Index comment = f.getAnnotation(Index.class);
            return comment.value();
        }
        return null;
    }

	private String getDefaultValue(Field f) {
        if (f.isAnnotationPresent(Default.class)) {
            Default defaultValue = f.getAnnotation(Default.class);
            return defaultValue.value();
        }
        return null;
    }

    private String getComment(Field f) {
        if(f.isAnnotationPresent(Comment.class)){
            Comment comment = f.getAnnotation(Comment.class);
            return comment.value();
        }
        return null;
    }

    private boolean hasNotNull(Field f) {
        return f.isAnnotationPresent(NotNull.class);
    }

    private  String generateCreateSql(String tableName, List<Column> columns) {
        //something like "CREATE TABLE `tbhello` (`id` int(11) NOT NULL auto_increment,`username` text,`sex` int(11) default NULL,`addr` varchar(40) default NULL,`birthday` datetime default NULL,`money` double default NULL,PRIMARY KEY  (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8";
        StringBuilder sql=new StringBuilder();
        sql.append("CREATE TABLE `");
        sql.append(tableName);
        sql.append("` (");

        for(Column column:columns){
            processName(sql,column);
            processType(sql,column);
            processUtf8mb4(sql,column);
            processNotNull(sql,column);
            processDefault(sql,column);
            processComment(sql,column);
            sql.append(",");
        }
        sql.append("PRIMARY KEY  (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8");
        return sql.toString();
    }

    private void processUtf8mb4(StringBuilder sql, Column column) {
    	if(column.utf8mb4){
             sql.append(" CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ");
    	}
	}

	private void processType(StringBuilder sql, Column column) {
        sql.append(" ");
        sql.append(column.sqlType);
    }

    private void processName(StringBuilder sql, Column column) {
        sql.append("`");
        sql.append(column.columnName);
        sql.append("`");
    }

    private void processComment(StringBuilder sql, Column column) {
        if(column.isPrimaryKey){
            return;
        }
        if(column.comment!=null){
            sql.append(" COMMENT '");
            sql.append(column.comment);
            sql.append(" '");
        }
    }

    private void processDefault(StringBuilder sql, Column column) {
        if(column.isPrimaryKey){
            return;
        }
        if(column.defaultValue!=null){
            sql.append(" DEFAULT '");
            sql.append(column.defaultValue);
            sql.append("'");
        }else{
            String defalutValue = getDefault(column);
            if(defalutValue==null){
                return;
            }
            sql.append(" DEFAULT ");
            sql.append(defalutValue);
        }
    }

    private String getDefault(Column column) {
        Type type=column.genericType;
        if(type==String.class){
            if(column.NotNull){
                return "''";
            }
            return "NULL";
        }
        else if(type==Integer.TYPE||type==Integer.class){
            return "0";
        }
        else if(type==Double.TYPE||type==Double.class){
            return "0";
        }
        else if(type==Boolean.TYPE||type==Boolean.class){
            return "0";
        }
        return null;
    }

    private void processNotNull(StringBuilder sql, Column column) {
        if(column.isPrimaryKey){
            return;
        }
        if(column.NotNull){
            sql.append(" NOT NULL");
        }
    }

    private  boolean needCreate(Field f) {
        return !f.isAnnotationPresent(NotCreate.class);
    }

    private  String getSqlType(Field f, boolean isPrimary) {
        if(isPrimary){
            if(f.isAnnotationPresent(PrimaryKey.class)){
                PrimaryKey type=f.getAnnotation(PrimaryKey.class);
                switch(type.value()){
                    case INCREMENT:
                    case IDENTITY:
                    case SEQUENCE:
                    case NATIVE:
                        return "int(11) NOT NULL auto_increment";
                    case ASSIGNED:
                        Class t=f.getType();
                        if(t==Integer.TYPE||t==Integer.class){
                            return "int(11) NOT NULL";
                        }else{
                            return "varchar(40) DEFAULT NULL";
                        }

                    case FOREIGN:
                        throw new RuntimeException("框架未处理外键");
                }
            }else{
                return "int(11) NOT NULL auto_increment";
            }
            return "";
        }

        if(f.isAnnotationPresent(FieldType.class)){
            FieldType type=f.getAnnotation(FieldType.class);
            switch(type.type()){
                case VARCHAR:
                    return "varchar("+type.length()+")";
                case TEXT:
                    return "TEXT";
                case MEDIUM_TEXT:
                    return "MEDIUMTEXT";
                case DATE:
                    return "DATE";
                case DATETIME:
                    return "DATETIME";
                case TIME:
                    return "TIME";
                case DECIMAL:
                    return "decimal("+type.length()+","+type.precision()+")";
                case INT:
                    return "int("+type.length()+")";
            }
        }

        Type type=f.getGenericType();
        if(type==String.class){
            return "varchar(40)";
        }
        else if(type==Integer.TYPE||type==Integer.class){
            return "int(11)";
        }
        else if(type==Double.TYPE||type==Double.class){
            return "decimal(12,2)";
        }
        else if(type==Date.class||type==java.sql.Date.class){
        	return "DATE";
        }
        else if(type==Time.class){
            return "TIME";
        }
        else if(type==Boolean.TYPE||type==Boolean.class){
            return "bit(1)";
        }
        else if(f.getType().isEnum()) {
            return "char(40)";
        } else {
            System.out.println(f.getType());
            System.out.println(f.getType().isEnum());
        }
        throw new RuntimeException("框架未处理的类型"+f.getGenericType());
    }

}

class Column {
    String columnName;
    String sqlType;
    boolean NotNull;
    String defaultValue;
    String comment;
    boolean isPrimaryKey;
    Type genericType;
    String index;
    boolean utf8mb4;


    public Column(String columnName, String sqlType) {
        this.columnName = columnName;
        this.sqlType = sqlType;
    }

    public Column(String columnName, String sqlType, boolean NotNull,
            String defaultValue, String comment,boolean isPrimaryKey,Type genericType,String index,boolean utf8mb4) {
        this.columnName = columnName;
        this.sqlType = sqlType;
        this.NotNull = NotNull;
        this.defaultValue = defaultValue;
        this.comment = comment;
        this.isPrimaryKey=isPrimaryKey;
        this.genericType=genericType;
        this.index=index;
        this.utf8mb4=utf8mb4;
    }
}
